package com.kaizzi.data.ppt;

import com.fasterxml.jackson.databind.JsonNode;
import com.kaizzi.data.common.been.NameDouble;
import com.kaizzi.data.common.been.SeriesData;
import com.kaizzi.data.common.utils.StringUtil;
import com.kaizzi.data.jdl.service.LogService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.sl.usermodel.PaintStyle;
import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.awt.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ProjectName: excel
 * @Description: description
 * @Date: 2020/7/22 0:26
 */
@Component
@Slf4j
public class TemplateUtil {

    @Resource
    private LogService logService;

    public void updateChartCatAndNum(SeriesData data, CTSerTx serTitle, CTAxDataSource catDataSource, CTNumDataSource numDataSource) {

        // 更新系列标题
        if (serTitle != null && serTitle.getStrRef() != null){
            serTitle.getStrRef().setF(serTitle.getStrRef().getF()); //
            serTitle.getStrRef().getStrCache().getPtArray(0).setV(data.name);
        }

        // TODO cat 也可能是 numRef
        long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();

        long ptCatCnt =0;
        if (catDataSource.getStrRef() ==null){
            ptCatCnt = catDataSource.getNumRef().getNumCache().getPtCount().getVal();
        }else {
            ptCatCnt = catDataSource.getStrRef().getStrCache().getPtCount().getVal();
        }

        int dataSize = data.value.size();
        for (int i = 0; i < dataSize; i++) {
            NameDouble cellValu = data.value.get(i);
            if (catDataSource.getStrRef() != null){
                CTStrVal cat = ptCatCnt > i ? catDataSource.getStrRef().getStrCache().getPtArray(i)
                        : catDataSource.getStrRef().getStrCache().addNewPt();
                cat.setIdx(i);
                cat.setV(cellValu.name);
            }else {
                CTNumVal cat = ptCatCnt > i ? catDataSource.getNumRef().getNumCache().getPtArray(i)
                        : catDataSource.getNumRef().getNumCache().addNewPt();
                cat.setIdx(i);
                cat.setV(cellValu.name);
            }

            CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i)
                    : numDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.2f", cellValu.value));

        }

        // 更新对应 excel 的range
        catDataSource.getStrRef().setF(
                replaceRowEnd(catDataSource.getStrRef().getF(),
                        ptCatCnt,
                        dataSize));
        numDataSource.getNumRef().setF(
                replaceRowEnd(numDataSource.getNumRef().getF(),
                        ptNumCnt,
                        dataSize));

        // 删除多的
        if (ptNumCnt > dataSize) {
            for (int idx = dataSize; idx < ptNumCnt; idx++) {
                catDataSource.getStrRef().getStrCache().removePt(dataSize);
                numDataSource.getNumRef().getNumCache().removePt(dataSize);
            }
        }
        // 更新个数
        catDataSource.getStrRef().getStrCache().getPtCount().setVal(dataSize);
        numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
    }

    /**
     * 折线图
     * @param plot
     * @param seriesDatas
     */
    public void lineChart(CTPlotArea plot , List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet, XSLFChart chart,int colNum) {

        System.out.println("折线图");
        List<CTLineChart> lineChartList = plot.getLineChartList();
        for (CTLineChart lineChart:lineChartList){
            List<CTLineSer> serList = lineChart.getSerList();

            try {
                updateChartExcelV3(seriesDatas, workbook, sheet,colNum);
                workbook.write(chart.getPackagePart().getOutputStream());
            }catch (Exception e){
                logService.setDataLogStatus("ppt-error");
                log.error("写入excel 异常,异常原因:{}",e);
            }

            int i =0;
            for (CTLineSer ser:serList){
                try {
                    updateChartCatAndNum(seriesDatas.get(i),ser.getTx(),ser.getCat(),ser.getVal());
                    i++;
                }catch (Exception e){
                    logService.setDataLogStatus("ppt-error");
                    log.error("折线图异常:{},",e);
                }
            }
        }
    }

    public void lineChart2(CTPlotArea plot , List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet, XSLFChart chart,int colNum) {

        System.out.println("折线图");
        List<CTLineChart> lineChartList = plot.getLineChartList();
        for (CTLineChart lineChart:lineChartList){
            List<CTLineSer> serList = lineChart.getSerList();

            try {
                updateChartExcelV(seriesDatas, workbook, sheet);
                workbook.write(chart.getPackagePart().getOutputStream());
            }catch (Exception e){
                logService.setDataLogStatus("ppt-error");
                log.error("写入excel 异常,异常原因:{}",e);
            }

            int i =0;
            for (CTLineSer ser:serList){
                try {
                    updateChartCatAndNum(seriesDatas.get(i),ser.getTx(),ser.getCat(),ser.getVal());
                    i++;
                }catch (Exception e){
                    logService.setDataLogStatus("ppt-error");
                    log.error("折线图异常");
                }
            }
        }
    }

    public void pieChart(CTPlotArea plot ,List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet, XSLFChart chart) {
        System.out.println("饼图");
        List<CTPieChart> pieChartList = plot.getPieChartList();
        for (CTPieChart pieChart:pieChartList){
            List<CTPieSer> serList = pieChart.getSerList();

            try {
                updateChartExcelV(seriesDatas, workbook, sheet);
                workbook.write(chart.getPackagePart().getOutputStream());
            }catch (Exception e){
                logService.setDataLogStatus("ppt-error");
                log.error("写入excel 异常,异常原因:{}",e);
            }
            int i=0;
            for (CTPieSer ser:serList){
                try {
                    updateChartCatAndNum(seriesDatas.get(i),ser.getTx(),ser.getCat(),ser.getVal());
                    i++;
                }catch (Exception e){
                    logService.setDataLogStatus("ppt-error");
                    log.error("饼图异常");
                }
            }
        }
    }

    /**
     * 条形图
     * @param plot
     * @param seriesDatas
     */
    public void barChart(CTPlotArea plot, List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet, XSLFChart chart) {

        log.info("柱形图");
        List<CTBarChart> barChartList = plot.getBarChartList();
        for (CTBarChart barChart:barChartList) {
            List<CTBarSer> serList = barChart.getSerList();
            try {
                updateChartExcelV(seriesDatas, workbook, sheet);
                workbook.write(chart.getPackagePart().getOutputStream());
            }catch (Exception e){
                logService.setDataLogStatus("ppt-error");
                log.error("写入excel 异常,异常原因:{}",e);
            }
            int i=0;
            for (CTBarSer ser:serList) {
                try {
                    updateChartCatAndNum(seriesDatas.get(i),ser.getTx(),ser.getCat(),ser.getVal());
                    i++;
                }catch (Exception e){
                    logService.setDataLogStatus("ppt-error");
                    log.error("柱形图异常:{}",i);
                    log.error("异常原因：{}",e);
                }
            }
        }
    }

    public void textArray(XSLFTextParagraph textParagraph, JsonNode paragraphData, boolean newLine){
        //段落样式
        XSLFTextRun enRun = textParagraph.getTextRuns().get(0);
        XSLFTextRun numRun = textParagraph.getTextRuns().get(1);
        PaintStyle fontColor = enRun.getFontColor();
        Double fontSize = enRun.getFontSize();
        Double fontSize1 = numRun.getFontSize();
        PaintStyle fontColor1 = numRun.getFontColor();
        enRun.setText("");
        numRun.setText("");
        for (int i=0;i<paragraphData.size();i++){
            String s = paragraphData.get(i).asText();
            String[] split = s.split("'");
            for (String dataStr:split) {
                XSLFTextRun textRun1 = textParagraph.addNewTextRun();
                if (StringUtil.hasDigitAll(dataStr)){
                    //数字
                    textRun1.setFontColor(fontColor1);
                    textRun1.setFontSize(fontSize1);
                    textRun1.setBold(true);
                    textRun1.setText(dataStr);
                    continue;
                }
                //非数字
                textRun1.setFontSize(fontSize);
                textRun1.setFontColor(fontColor);
                textRun1.setBold(false);
                textRun1.setText(dataStr);
            }
            //是否换行
            if (newLine){
                XSLFTextRun spaceRun = textParagraph.addNewTextRun();
                XSLFTextRun spaceRun1 = textParagraph.addNewTextRun();
                spaceRun.setFontSize(11d);
                spaceRun1.setFontSize(11d);
                spaceRun.setText("\r");
                spaceRun1.setText("\r");
            }
        }
    }

    public void text(XSLFTextParagraph textParagraph,String data,boolean newLine){
        XSLFTextRun enRun = textParagraph.getTextRuns().get(0);
        XSLFTextRun numRun = textParagraph.getTextRuns().get(1);
        PaintStyle fontColor = enRun.getFontColor();
        Double fontSize = enRun.getFontSize();
        boolean bold = enRun.isBold();
        boolean bold1 = numRun.isBold();
        boolean underlined = enRun.isUnderlined();
        boolean underlined1 = numRun.isUnderlined();
        Double fontSize1 = numRun.getFontSize();
        PaintStyle fontColor1 = numRun.getFontColor();
        enRun.setText("");
        numRun.setText("");
        String[] split = data.split("'");
        int count = 0;
        for (String dataStr:split) {
            XSLFTextRun textRun1 = textParagraph.addNewTextRun();
            if (StringUtil.hasDigitAll(dataStr) || count%2 !=0){
                //数字
                textRun1.setFontColor(fontColor1);
                textRun1.setFontSize(fontSize1);
                textRun1.setBold(bold1);
                textRun1.setText(dataStr);
                textRun1.setUnderlined(underlined1);
//                textRun1.setFontFamily("微软雅黑");
                count++;
                continue;
            }
            //非数字
            textRun1.setFontSize(fontSize);
            textRun1.setFontColor(fontColor);
            textRun1.setBold(bold);
            textRun1.setText(dataStr);
//            textRun1.setFontFamily("微软雅黑");
            textRun1.setUnderlined(underlined);
            count++;
        }
        //是否换行
        if (newLine){
            XSLFTextRun spaceRun = textParagraph.addNewTextRun();
            XSLFTextRun spaceRun1 = textParagraph.addNewTextRun();
            spaceRun.setFontSize(11d);
            spaceRun1.setFontSize(11d);
            spaceRun.setText("\r");
            spaceRun1.setText("\r");
        }
    }

    /**
     * 箭头更改
     * @param trend
     * @param shape
     */
    public void replaceSimpleShape(int trend, XSLFShape shape) {
        if (trend == 3){
            ((XSLFSimpleShape) shape).setRotation(180);
            ((XSLFSimpleShape) shape).setFillColor(Color.RED);
        }
        if (trend == 2){
            ((XSLFSimpleShape) shape).setFillColor(new Color(0,176,80));
        }
        if (trend == 1 || trend == 0){
            Color color = new Color(0, 0, 0,0);
            ((XSLFSimpleShape) shape).setFillColor(color);
        }
    }


    /**
     * 更新图表的关联 excel， 值是纵向的
     *
     * @param workbook
     * @param sheet
     */
    protected void updateChartExcelV(List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet) {
        for (int i = 0; i < seriesDatas.size(); i++) {
            SeriesData data = seriesDatas.get(i);
//            if (data.name != null && !data.name.isEmpty()) {
            // 系列名称，不能修改，修改后无法打开 excel
//                title.getCell(i + 1).setCellValue(data.name);
//            }
            int size = data.value.size();
            for (int j = 0; j < size; j++) {
                XSSFRow row = sheet.getRow(j + 1);
                if (row == null) {
                    row = sheet.createRow(j + 1);
                }
                NameDouble cellValu = data.value.get(j);
                XSSFCell cell = row.getCell(0);
                if (cell == null) {
                    cell = row.createCell(0);
                }
                cell.setCellValue(cellValu.name);

                cell = row.getCell(i + 1);
                if (cell == null) {
                    cell = row.createCell(i + 1);
                }
                cell.setCellValue(cellValu.value);
            }
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
        }
    }

    /**
     * 更新图表的关联 excel， 值是纵向的
     *
     * @param workbook
     * @param sheet
     */
    protected void updateChartExcelV3(List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet,int colNo) {
        for (int i = 0; i < seriesDatas.size(); i++) {
            //折线图再第几列下标-1
            i = colNo;
            SeriesData data = seriesDatas.get(0);
            int size = data.value.size();
            for (int j = 0; j < size; j++) {
                XSSFRow row = sheet.getRow(j + 1);
                if (row == null) {
                    row = sheet.createRow(j + 1);
                }
                NameDouble cellValu = data.value.get(j);
                XSSFCell cell = row.getCell(0);
                if (cell == null) {
                    cell = row.createCell(0);
                }
                cell.setCellValue(cellValu.name);

                cell = row.getCell(i + 1);
                if (cell == null) {
                    cell = row.createCell(i + 1);
                }
                cell.setCellValue(cellValu.value);
            }
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    log.info("删除的行为:{}",idx);
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
        }
    }

    /**
     * 替换 形如： Sheet1!$A$2:$A$4 的字符
     *
     * @param range
     * @return
     */
    public static String replaceRowEnd(String range, long oldSize, long newSize) {
        Pattern pattern = Pattern.compile("(:\\$[A-Z]+\\$)(\\d+)");
        Matcher matcher = pattern.matcher(range);
        if (matcher.find()) {
            long old = Long.parseLong(matcher.group(2));
            return range.replaceAll("(:\\$[A-Z]+\\$)(\\d+)", "$1" + Long.toString(old - oldSize + newSize));
        }
        return range;
    }

    //字体样式
    public void setTextStyle(XSLFTextRun textRun,XSLFTextRun originalTextRun,String text){
        textRun.setFontColor(originalTextRun.getFontColor());
        textRun.setFontSize(originalTextRun.getFontSize());
        textRun.setBold(originalTextRun.isBold());
        textRun.setText(text);
    }

    //换行 不空行
    public void wrapLine(XSLFTextParagraph textParagraph){
        XSLFTextRun spaceRun = textParagraph.addNewTextRun();
        XSLFTextRun spaceRun1 = textParagraph.addNewTextRun();
        spaceRun.setFontSize(11d);
        spaceRun1.setFontSize(11d);
        spaceRun.setText("\r");
        spaceRun1.setText("");
    }

    //获取原始text stytle
    public List<XSLFTextRun> getOriginalTextRun(List<XSLFTextParagraph> textParagraphs){
        XSLFTextRun enRun = textParagraphs.get(0).getTextRuns().get(0);
        XSLFTextRun numRun = textParagraphs.get(0).getTextRuns().get(1);
        List<XSLFTextRun> textRuns = new ArrayList<>();
        textRuns.add(enRun);
        textRuns.add(numRun);
        enRun.setText("");
        numRun.setText("");
        return textRuns;
    }

}
